﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBEngine.Elements;


namespace DBEngine.Tables
{
	class ClientInfoTable : Table
	{
		static Dictionary<ClientInfo.Fields, SqlDbType> mTableFields;

		static ClientInfoTable()
		{
			mTableFields = new Dictionary<ClientInfo.Fields, SqlDbType>();
			mTableFields.Add(ClientInfo.Fields.ID, SqlDbType.Int);
			mTableFields.Add(ClientInfo.Fields.FirstName, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.LastName, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.Address, SqlDbType.VarChar);
            mTableFields.Add(ClientInfo.Fields.Tel, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.Mobile, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.Email, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.DateOfBirth, SqlDbType.DateTime);
			mTableFields.Add(ClientInfo.Fields.Gender, SqlDbType.VarChar);
			mTableFields.Add(ClientInfo.Fields.MembershipID, SqlDbType.Int);

		}
		
        internal ClientInfoTable() : base("ClientInfo") { }

        override protected string SetInsertQuery(Element newElement, string cols, string values, List<SqlParameter> paramsList)
        {
            ClientInfo clientInfo = (ClientInfo)newElement;
            string retval = "";

            int i = 0;
            string paramName;
            ClientInfo.Fields colName;
            SqlParameter param;
            foreach (KeyValuePair<ClientInfo.Fields, SqlDbType> pair in mTableFields)
            {
                colName = pair.Key;
                paramName = @"@" + colName + "Param";

                if (clientInfo[colName] == null || colName == ClientInfo.Fields.ID)
                    continue;

                if (i++ > 0)
                {
                    cols += ", ";
                    values += ", ";
                }
                cols += colName;
                values += paramName;
                param = new SqlParameter(paramName, pair.Value);
                param.Value = clientInfo[colName];
                paramsList.Add(param);

            }
            cols += ")";
            values += ")";
            retval += cols + " " + values + @"; Select @@IDENTITY";
            return retval;
        }
    

        override protected string SetUpdateQuery(Element element, List<SqlParameter> paramsList)
        {
            ClientInfo clientInfo = (ClientInfo)element;
            string retval = "";
            string updateString = "";
            int i = 0;

            ClientInfo.Fields colName;
            string paramName;
            SqlParameter param;
            
            foreach (KeyValuePair<ClientInfo.Fields, SqlDbType> pair in mTableFields)
            {
                colName = pair.Key;
                paramName = @"@" + colName + "Param";

                if (clientInfo[colName] == null || colName == ClientInfo.Fields.ID)
                    continue;
                if (i == 0)
                {
                    updateString += "Set ";
                }
                if (i++ > 0)
                {
                    updateString += ", ";
                }

                updateString += colName + " = " + paramName;

                param = new SqlParameter(paramName, pair.Value);
                param.Value = clientInfo[colName];
                paramsList.Add(param);

            }

            retval= updateString;
            return retval ;
        }
		

		override protected Object ReadRow(SqlDataReader reader)
		{
			ClientInfo retval = new ClientInfo();
			foreach (ClientInfo.Fields colName in mTableFields.Keys)
			{
				retval[colName] = reader[colName.ToString()];
			}

			return retval;
		}

        //override internal Boolean Insert(Object newObj)
        //{
        //    ClientInfo clientInfo = (ClientInfo)newObj;
        //    Boolean retval = false;

        //    if (clientInfo == null)
        //        return retval;


        //    string queryString = @"Insert into " + mTableName + " ";
        //    string cols = "(", values = "Values(";
        //    List<SqlParameter> paramsList = new List<SqlParameter>();

        //    int i = 0;
        //    string paramName;
        //    ClientInfo.Fields colName;
        //    SqlParameter param;
        //    foreach (KeyValuePair<ClientInfo.Fields, SqlDbType> pair in mTableFields)
        //    {


        //        colName = pair.Key;
        //        paramName = @"@" + colName + "Param";

        //        if (clientInfo[colName] == null || colName == ClientInfo.Fields.ID)
        //            continue;

        //        if (i++ > 0)
        //        {
        //            cols += ", ";
        //            values += ", ";
        //        }
        //        cols += colName;
        //        values += paramName;
        //        param = new SqlParameter(paramName, pair.Value);
        //        param.Value = clientInfo[colName];
        //        paramsList.Add(param);

        //    }
        //    cols += ")";
        //    values += ")";
        //    queryString += cols + " " + values + @"; Select @@IDENTITY";

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    foreach (SqlParameter parameter in paramsList)
        //    {
        //        command.Parameters.Add(parameter);
        //    }

        //    Object dbResult = command.ExecuteScalar();
        //    if (dbResult == null)
        //        return retval;

        //    retval = true;
        //    clientInfo[ClientInfo.Fields.ID] = dbResult;


        //    return retval;
        //}

        //override internal Boolean Update(Object newObj)
        //{
        //    ClientInfo clientInfo = (ClientInfo)newObj;
        //    Boolean retval = false;

        //    if (clientInfo == null)
        //        return retval;


        //    string queryString = @"Update  " + mTableName + " ";
        //    string updateString = "";
        //    List<SqlParameter> paramsList = new List<SqlParameter>();

        //    int i = 0;
        //    ClientInfo.Fields colName;
        //    string paramName;
        //    SqlParameter param;
        //    foreach (KeyValuePair<ClientInfo.Fields, SqlDbType> pair in mTableFields)
        //    {


        //        colName = pair.Key;
        //        paramName = @"@" + colName + "Param";

        //        if (clientInfo[colName] == null || colName == ClientInfo.Fields.ID)
        //            continue;
        //        if (i == 0)
        //        {
        //            updateString += "Set ";
        //        }
        //        if (i++ > 0)
        //        {
        //            updateString += ", ";
        //        }

        //        updateString += colName + " = " + paramName;

        //        param = new SqlParameter(paramName, pair.Value);
        //        param.Value = clientInfo[colName];
        //        paramsList.Add(param);

        //    }

        //    queryString += updateString + "  where ID =@IDParam";
        //    param = new SqlParameter("@IDParam", mTableFields[ClientInfo.Fields.ID]);
        //    param.Value = clientInfo[ClientInfo.Fields.ID];
        //    paramsList.Add(param);

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    foreach (SqlParameter parameter in paramsList)
        //    {
        //        command.Parameters.Add(parameter);
        //    }

        //    command.ExecuteNonQuery();
        //    return true;
        //}

		//internal  ClientInfo Retrive(int id)
		//{

		//    ClientInfo retval = new ClientInfo();
		//    string queryString = @"Select * From " + mTableName + " Where ID =@IDParam ";

		//    SqlParameter param = new SqlParameter("@IDParam", mTableFields[ClientInfo.Fields.ID]);
		//    param.Value = id;

		//    SqlCommand command = DBConnection.getCommand();
		//    command.CommandText = queryString;
		//    command.Parameters.Add(param);

		//    SqlDataReader reader = command.ExecuteReader();
		//    while (reader.Read())
		//    {
		//        retval = (ClientInfo)ReadRow(reader);
		//    }
		//    reader.Close();
		//    return retval;
		//}


	}


}
